import json
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, Response
from security.decoration import certification
from models.logs.logs import Logs
from public.local import date_type
from datetime import datetime, timedelta
from sqlalchemy import and_
from typing import List
from urllib.parse import unquote, quote
from public.local import local_time


@api_routes.route("/logs", methods=["GET"])
@certification(auth=True, role=None)
def func_logs_get_api():
    """
    查询系统日志信息, 只要有token 都可以访问, 完成API开发任务
    http://127.0.0.1:8080/api/logs
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "start"        string          开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page}
    ---
    tags:
      - 系统日志
    """
    size = request.args.get("size", default=10, type=int)
    index = request.args.get("index", default=1, type=int)
    start = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None:
        arg0 = date_type()
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    if finish is None:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    count = g.session.query(Logs.id).filter(and_(Logs.time >= arg0, Logs.time <= arg1)).count()
    page: int = int((count / size) + 1) if (count / size) > int(count / size) else int(count / size)

    data: List[Logs] = g.session.query(Logs).filter(and_(
                            Logs.time >= arg0, Logs.time <= arg1
                        )).order_by(Logs.id).offset((index - 1) * size).limit(size).all()
    resp: list = []
    for index, itr in enumerate(data):
        resp.append(json.loads(itr.__repr__()))

    doc: dict = {
        "log_id": "数据库ID",
        "route": "访问后端接口API",
        "method": "访问方式",
        "name": "API接口名称",
        "user": "操作用户",
        "data": "操作参数",
        "time": "操作时间",
        "obsolescence": "旧的数据"
    }
    return jsonify({'status': 200, 'data': resp, "index": index, "size": size, "total": count, "pages": page, "doc": doc})


@api_routes.route("/logs/output", methods=["GET"])
# @certification(auth=True, role=None)
def func_logs_output_get_api():
    """
    导出系统日志信息成EXCEL, 只要有token 都可以访问, 完成API开发任务
    http://127.0.0.1:8080/api/logs/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "file"         string         导出文件名，没有默认按照今天日期
    "start"        string          开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"       string          结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
    ---
    tags:
      - 系统日志
    """
    file: str = request.args.get("file", default=None, type=str)
    start = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)
    arg0, arg1 = start, finish

    if start is None:
        var = 123
        arg0 = date_type()
    else:
        arg0 = datetime.strptime(finish, "%Y-%m-%d")

    if finish is None:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(start, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    data: List[Logs] = g.session.query(Logs).filter(and_(Logs.time >= arg0, Logs.time <= arg1)).order_by(Logs.id).all()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("系统日志历史数据表")
    worksheet_index.set_column("A:ZZ", 32)

    message: list = ["请求路径", "请求方式", "操作名称", "操作用户", "旧数据", "请求操作参数", "记录创建时间"]
    worksheet_index.write_row(row=0, col=0, data=message)

    for index, itr in enumerate(data):
        sequence = index + 1
        db_obsolescence = str(itr.obsolescence) if itr.obsolescence else None
        db_data = str(itr.data) if itr.data else None

        temp = [
            itr.route, itr.method, itr.name, itr.user,
            db_obsolescence, db_data, itr.time.strftime("%Y-%m-%d %H:%M:%S")
        ]
        worksheet_index.write_row(row=sequence, col=0, data=temp)

    file_name = "系统日志{}.xlsx".format(local_time())
    if file is not None:
        file_name = "{}.xlsx".format(file)

    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
